package persistencia;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import javax.swing.UIManager;
import outros.Conexao;
import transporte.TOPatrimonio;
import transporte.TOTransferencia;

public class DAOTransferencia {
    private Conexao conexao = new Conexao();
    private Connection con = null;

    public boolean abrirConexao() {
        UIManager.put("OptionPane.yesButtonText", "Sim");
        UIManager.put("OptionPane.noButtonText", "Não");
        UIManager.put("OptionPane.cancelButtonText", "Cancelar");
        try {
            conexao.abrirConexao();
            con = conexao.getConexao();
            return true;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Erro ao tentar conectar ao banco de dados.");
            return false;
        }
    }

    public String criarRegistro(ArrayList<TOTransferencia> trans) {
        PreparedStatement ps = null;
        String sql = "INSERT INTO patri_reg_transferencia VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        try {
            abrirConexao();
            int id = getProximo();
            if (id!=0) {
                for (int i=0;i<trans.size();i+=1) {
                    System.out.println(trans.get(i).getPatrimonio().getNumero());
                    ps = con.prepareStatement(sql);
                    ps.setInt(1, id);
                    ps.setInt(2, trans.get(i).getSetorOrigem());
                    ps.setInt(3, trans.get(i).getSetorDestino());
                    mudarLotacao(trans.get(i).getPatrimonio(), trans.get(i).getSetorDestino());
                    ps.setDate(4, new java.sql.Date(trans.get(i).getData().getTime()));
                    ps.setInt(5, trans.get(i).getUsuario());                    
                    ps.setString(6, trans.get(i).getHora());                    
                    ps.setInt(7, trans.get(i).getPatrimonio().getOrgao());
                    ps.setInt(8, trans.get(i).getPatrimonio().getArea());
                    ps.setInt(9, trans.get(i).getPatrimonio().getGrupo());
                    ps.setInt(10, trans.get(i).getPatrimonio().getSubgrupo());
                    ps.setInt(11, trans.get(i).getPatrimonio().getNumero());
                    ps.execute();
                }
                con.commit();
                conexao.fecharConexao();
                return "";
            } else {
                conexao.fecharConexao();
                JOptionPane.showMessageDialog(null, "Erro ao tentar atribuir novo ID.");
                return "erro";
            }
        } catch (Exception e) {
            conexao.fecharConexao();
            JOptionPane.showMessageDialog(null, "Erro ao tentar inserir registro. Detalhes: "+e.toString());
            return "erro";
        }
    }

    private void mudarLotacao(TOPatrimonio pat, int novaLotacao) {
        PreparedStatement ps = null;
        String sql = "UPDATE patri_patrimonio SET lotacao=? WHERE orgao=? AND area=? AND "+
                     "grupo=? AND subgrupo=? AND numero=?";
        try {
            abrirConexao();
            ps = con.prepareStatement(sql);
            ps.setInt(1, novaLotacao);
            ps.setInt(2, pat.getOrgao());
            ps.setInt(3, pat.getArea());
            ps.setInt(4, pat.getGrupo());
            ps.setInt(5, pat.getSubgrupo());
            ps.setInt(6, pat.getNumero());
            ps.execute();
            con.commit();
            conexao.fecharConexao();
        } catch (Exception e) {
            conexao.fecharConexao();
            JOptionPane.showMessageDialog(null, "Erro ao tentar mudar a garantia. Detalhes: "+e.toString());
        }
    }

    private int getProximo(){
        String sql = "SELECT MAX(numero) FROM patri_reg_transferencia";
        try {
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery(sql);
            if (rs.first()){
                return rs.getInt(1)+1;
            } else {
                return 0;
            }
        } catch (Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro", JOptionPane.ERROR_MESSAGE);
            return 0;
        }
    }
}
